﻿using System;
using System.Linq;
using System.Data.Linq;
using UniMana.Utils.Controls;
using UniMana.Data;
namespace UniMana.Data
{
    public static class Training_SubjectOpenRequestData
    {
        /// Lấy danh sách CTĐT Có Chứa Môn
        /// <param name="nTraining_SubjectOpenRequest"></param>
        /// <param name="nTraining_TrainingProgram_Detail"></param>
        /// <param name="nTraining_TrainingProgram"></param>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="nCatalogue_Department"></param>
        /// <param name="_iTraining_SubjectKey"></param>
        /// <param name="_iCatalogue_DepartmentKey"></param>
        /// <returns></returns>
        public static IQueryable GetProgramBySubjectKey(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            Table<Catalogue_Department> nCatalogue_Department,
            int _iTraining_SubjectKey)
        {
            var detail = from pr_de in nTraining_TrainingProgram_Detail
                         join pr in nTraining_TrainingProgram
                            on pr_de.Training_TrainingProgramKey
                            equals pr.Training_TrainingProgramKey
                         join maj in nCatalogue_Major
                            on pr.Catalogue_MajorKey
                            equals maj.Catalogue_MajorKey
                         join de in nCatalogue_Department
                            on maj.Catalogue_DepartmentKey
                            equals de.Catalogue_DepartmentKey
                         group new { pr, pr_de, de } by new
                         {
                             pr.Training_TrainingProgramKey,
                             pr.ID,
                             TrainingProgramName = pr.Name,
                             pr_de.Training_SubjectKey,
                             de.Catalogue_DepartmentKey,
                             pr.Catalogue_CourseKey,
                             pr.Catalogue_LevelKey,
                             pr.Catalogue_MajorKey,
                             pr.Catalogue_TrainingTypeKey
                         } into g
                         where g.Key.Training_SubjectKey == _iTraining_SubjectKey
                         select new
                         {
                             g.Key.Training_TrainingProgramKey,
                             g.Key.ID,
                             g.Key.TrainingProgramName,
                             g.Key.Catalogue_DepartmentKey,
                             g.Key.Catalogue_CourseKey,
                             g.Key.Catalogue_LevelKey,
                             g.Key.Catalogue_MajorKey,
                             g.Key.Catalogue_TrainingTypeKey
                         };
            return detail.Distinct();
        }

        public static IQueryable GetListByCourseAndTrainingProgram(
            this Table<Training_SubjectOpenRequest> nRequestOpen,
            Table<Training_Subject> nSubject,
            Table<Training_SubjectOpenRequestMessenger> nTraining_SubjectOpenRequestMessenger, 
            int _iTrainingProgram,
            bool _bAgree = false)
        {
            var iQuery = from t in nRequestOpen
                         join t0 in nSubject 
                            on t.Training_SubjectKey
                            equals t0.Training_SubjectKey
                         join b in
                             (
                                 (from t01 in nTraining_SubjectOpenRequestMessenger
                                  where
                                    t01.UnRead == true && t01.Reply == false
                                  group t01 by new
                                  {
                                      t01.Training_SubjectOpenRequestKey
                                  } into g
                                  select new
                                  {
                                      NoMessenger = (Int64?)g.Count(),
                                      g.Key.Training_SubjectOpenRequestKey
                                  })) 
                            on t.Training_SubjectOpenRequestKey
                            equals b.Training_SubjectOpenRequestKey into b_join
                         from b in b_join.DefaultIfEmpty()
                         where t.Training_TrainingProgramKey == _iTrainingProgram && 
                                t.AgreeOpen == _bAgree
                         select new
                         {
                             t.Training_SubjectOpenRequestKey,
                             t.Training_SubjectKey,
                             t.AgreeOpen,
                             b.NoMessenger,
                             t0.ID,
                             t0.Name,
                             t.Training_TrainingProgramKey
                         };
            return iQuery;
        }

        ///Lấy danh sách các môn yêu cầu mở
        /// <param name="nTraining_SubjectOpenRequest"></param>
        /// <param name="nTraining_Subject"></param>
        /// <param name="nTraining_TrainingProgram"></param>
        /// <param name="nTraining_TrainingProgram_Detail"></param>
        /// <param name="nTraining_SubjectOpenRequestMessenger"></param>
        /// <param name="_iCourserKey">Mã khóa học</param>
        /// <returns></returns>
        public static IQueryable GetListCheckBeforeOpen(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest,
            Table<Training_Subject> nTraining_Subject,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_SubjectOpenRequestMessenger> nTraining_SubjectOpenRequestMessenger,
            int _iCourserKey)
        {
            var iQuery1 = from t in nTraining_TrainingProgram_Detail
                          join t0 in nTraining_SubjectOpenRequest
                                on new {    t.Training_SubjectKey, 
                                            t.Training_TrainingProgramKey }
                                equals new { Training_SubjectKey = 
                                                Convert.ToInt32(t0.Training_SubjectKey), 
                                            Training_TrainingProgramKey = 
                                                Convert.ToInt32(t0.Training_TrainingProgramKey) }
                          where
                            t0.AgreeOpen == true
                          select new
                          {
                              t.Training_SubjectKey,
                              t0.AgreeOpen,
                              t.Training_TrainingProgramKey
                          };

            var B = (from t in nTraining_TrainingProgram_Detail
                     join t0 in nTraining_TrainingProgram 
                        on t.Training_TrainingProgramKey 
                        equals t0.Training_TrainingProgramKey
                     where
                       t0.Catalogue_CourseKey == _iCourserKey &&
                       ! (
                            from query1 in iQuery1
                            where query1.Training_TrainingProgramKey == t0.Training_TrainingProgramKey
                            select query1.Training_SubjectKey
                         ).Contains(t.Training_SubjectKey)
                     select new
                     {
                         t0.Catalogue_MajorKey,
                         t.Training_SubjectKey,
                         t0.Training_TrainingProgramKey,
                         t0.Name
                     });
            var iQuery = from a in
                             (
                                 (from t in nTraining_SubjectOpenRequest
                                  join t0 in nTraining_Subject 
                                    on new { Training_SubjectKey = 
                                        Convert.ToInt32(t.Training_SubjectKey) } 
                                    equals new { Training_SubjectKey = t0.Training_SubjectKey }
                                  join t1 
                                        in nTraining_TrainingProgram 
                                        on t.Training_TrainingProgramKey
                                        equals t1.Training_TrainingProgramKey
                                  where
                                    t.Catalogue_CourseKey == _iCourserKey 
                                    && t.AgreeOpen == false
                                  select new
                                  {
                                      t.Training_SubjectOpenRequestKey,
                                      t1.Catalogue_MajorKey,
                                      Training_SubjectKey = (System.Int32?)t0.Training_SubjectKey,
                                      t0.ID,
                                      t0.Name
                                  }))
                         join b in B on a.Training_SubjectKey equals b.Training_SubjectKey into b_join
                         from b in b_join.DefaultIfEmpty()
                         orderby
                           a.Training_SubjectKey
                         select new
                         {
                             a.Training_SubjectOpenRequestKey,
                             Training_SubjectKey = (System.Int32?)a.Training_SubjectKey,
                             a.ID,
                             a.Name,
                             a.Catalogue_MajorKey,
                             MajorKey = (System.Int32?)b.Catalogue_MajorKey,
                             b.Training_TrainingProgramKey,
                             TrainingProgramName = b.Name
                         };
            var Messenger = from t in nTraining_SubjectOpenRequestMessenger
                            where
                              t.UnRead == true &&
                              t.Reply == true
                            group t by new
                            {
                                t.Training_SubjectOpenRequestKey,
                                t.Reply,
                                t.UnRead
                            } into g
                            select new
                            {
                                Training_SubjectOpenRequestKey = 
                                    (System.Int32?)g.Key.Training_SubjectOpenRequestKey,
                                NoMessenger = (Int64?)g.Count()
                            };
            var lastResult = from query in iQuery
                             from mess in Messenger.Where(
                                p => query.Training_SubjectOpenRequestKey == 
                                    p.Training_SubjectOpenRequestKey).DefaultIfEmpty()
                             select new
                             {
                                 query.Training_SubjectOpenRequestKey,
                                 query.Training_SubjectKey,
                                 query.ID,
                                 query.Name,
                                 query.Catalogue_MajorKey,
                                 MajorKey = query.MajorKey == null 
                                    ? 0 : query.MajorKey,
                                 Training_TrainingProgramKey = query.Training_TrainingProgramKey == null 
                                    ? 0: query.Training_TrainingProgramKey,
                                 TrainingProgramName = query.TrainingProgramName == null 
                                    ? "" : query.TrainingProgramName,
                                 NoMessenger = mess.NoMessenger == null ? 0 : mess.NoMessenger
                             };
                             


            //var Result = from query in iQuery
            //             join mess in Messenger on query.Training_SubjectOpenRequestKey 
                            //equals mess.Training_SubjectOpenRequestKey
            //             select new
            //             {
            //                 query.Training_SubjectOpenRequestKey,
            //                 query.Training_SubjectKey,
            //                 query.ID,
            //                 query.Name,
            //                 query.Catalogue_MajorKey,
            //                 query.MajorKey,
            //                 query.TrainingProgramName,
            //                 mess.NoMessenger
            //             };
            return lastResult;
        }

        /// Duyệt các môn yêu cầu
        /// <param name="nSubjectOpenRequest"></param>
        /// <param name="_iSubjectKey">Mã môn yêu cầu</param>
        /// <param name="_iOpenKey">Mã mở</param>
        public static void AgreeSubject(
            this Table<Training_SubjectOpenRequest> nSubjectOpenRequest,  
            int _iSubjectKey,
            int _iOpenKey = -1)
        {
            DBMain _DBMain = new DBMain();
            var iQuery = from requestopen in _DBMain.m_DCSave.Training_SubjectOpenRequests
                         where requestopen.AgreeOpen == false && 
                                requestopen.Training_SubjectKey == _iSubjectKey
                         select requestopen;
            bds _bds = new bds();
            _bds.DataSource = iQuery;
            for (int i = 0; i < _bds.Count; i++)
            {
                Training_SubjectOpenRequest request = _bds[i] as Training_SubjectOpenRequest;
                request.AgreeOpen = true;
                if (_iOpenKey != -1)
                {
                    request.Training_SubjectOpenKey = _iOpenKey;
                }
            }
            _bds.EndEdit();
            _DBMain.m_DCSave.SubmitChanges();
        }

        /// Xóa môn mở
        /// <param name="nSubjectOpenRequest"></param>
        /// <param name="_iSubjectOpenKey"></param>
        public static void DeleteSubjectOpen(
            this Table<Training_SubjectOpenRequest> nSubjectOpenRequest, 
            int _iSubjectOpenKey)
        {
            DBMain _DBMain = new DBMain();
            var iQuery = from requestopen in _DBMain.m_DCSave.Training_SubjectOpenRequests
                         where requestopen.Training_SubjectOpenKey == _iSubjectOpenKey
                         select requestopen;
            bds _bds = new bds();
            _bds.DataSource = iQuery;
            for (int i = 0; i < _bds.Count; i++)
            {
                Training_SubjectOpenRequest request = _bds[i] as Training_SubjectOpenRequest;
                request.AgreeOpen = false;
                if (_iSubjectOpenKey != -1)
                {
                    request.Training_SubjectOpenKey = null;
                }
            }
            _bds.EndEdit();
            _DBMain.m_DCSave.SubmitChanges();
        }

        public static bool UpdateFinish(
            this Table<Training_SubjectOpenRequest> nSubjectOpenRequest)
        {
            int iFinish = 2;
            DBMain _DBMain = new DBMain();
            var iQuery = from subjectOpenReqeust in nSubjectOpenRequest
                         where subjectOpenReqeust.Startus == 0
                         select subjectOpenReqeust;
            if (iQuery.Count() > 0)
            {
                return false;
            }
            var iQuery1 = from subjectOpenReqeust 
                              in _DBMain.m_DCSave.Training_SubjectOpenRequests
                         where subjectOpenReqeust.Startus == 1
                         select subjectOpenReqeust;
            bds _bds = new bds();
            _bds.DataSource = iQuery1;
            for (int i = 0; i < _bds.Count; i++)
            {
                Training_SubjectOpenRequest request = 
                    _bds[i] as Training_SubjectOpenRequest;
                request.Startus = iFinish;
            }
            _bds.EndEdit();
            _DBMain.m_DCSave.SubmitChanges();
            return true;
        }

        /// Lấy danh sách khoa yêu cầu mở môn
        /// <param name="nTraining_SubjectOpenRequest"></param>
        /// <param name="nCatalogue_Department"></param>
        /// <param name="_iTraining_SubjectKey"></param>
        /// <returns></returns>
        public static IQueryable GetDepartmentRequestBySubjectKey(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest,
            Table<Catalogue_Department> nCatalogue_Department, 
            int _iTraining_SubjectKey)
        {
            var iQuery = from sj_o_rq in nTraining_SubjectOpenRequest
                         join de in nCatalogue_Department 
                            on sj_o_rq.Catalogue_DepartmentKey
                            equals de.Catalogue_DepartmentKey
                         where
                           sj_o_rq.Training_SubjectKey == _iTraining_SubjectKey
                           && sj_o_rq.AgreeOpen == false
                         group de by new
                         {
                             de.Name
                         } into g
                         select new
                         {
                             g.Key.Name
                         };
            return iQuery;
        }

        ///Lấy danh sách khoa chưa yêu cầu mở môn
        /// <param name="nTraining_SubjectOpenRequest"></param>
        /// <param name="nTraining_TrainingProgram_Detail"></param>
        /// <param name="nTraining_TrainingProgram"></param>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="nCatalogue_Department"></param>
        /// <param name="_iTraining_SubjectKey"></param>
        /// <param name="_iCatalogue_DepartmentKey"></param>
        /// <returns></returns>
        public static IQueryable GetDepartmentNotRequestBySubjectKey(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            Table<Catalogue_Department> nCatalogue_Department,
            int _iTraining_SubjectKey,
            int _iCatalogue_DepartmentKey)
        {
            var detail = from pr_de in nTraining_TrainingProgram_Detail
                         join pr in nTraining_TrainingProgram 
                            on pr_de.Training_TrainingProgramKey 
                            equals pr.Training_TrainingProgramKey
                         join maj in nCatalogue_Major 
                            on pr.Catalogue_MajorKey
                            equals maj.Catalogue_MajorKey 
                         join de in nCatalogue_Department 
                            on maj.Catalogue_DepartmentKey
                            equals de.Catalogue_DepartmentKey
                         group new { t = pr_de, t2 = de } by new
                         {
                             pr_de.Training_TrainingProgramKey,
                             pr_de.Training_SubjectKey,
                             de.Name,
                             de.Catalogue_DepartmentKey
                         } into g
                         where g.Key.Training_SubjectKey == _iTraining_SubjectKey
                         select new
                         {
                             g.Key.Training_TrainingProgramKey,
                             Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                             g.Key.Name,
                             g.Key.Catalogue_DepartmentKey
                         };
            var iQuery = from det in detail
                         where
                         !(
                            from o_rq in nTraining_SubjectOpenRequest
                            
                            where
                                o_rq.Training_SubjectKey == det.Training_SubjectKey && o_rq.AgreeOpen == true && 
                                o_rq.Training_TrainingProgramKey == det.Training_TrainingProgramKey
                            select
                                o_rq.Training_TrainingProgramKey
                         ).Contains(det.Training_TrainingProgramKey)
                         && det.Catalogue_DepartmentKey != _iCatalogue_DepartmentKey
                         select det.Name;
            return iQuery.Distinct();
        }

        public static IQueryable GetListByDateTime(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest, 
            DateTime _dteFrom, 
            DateTime _dteEnd)
        {
            var iQuery = from subjectOpenRequest in nTraining_SubjectOpenRequest
                         where subjectOpenRequest.CreateOn >= _dteFrom
                         && subjectOpenRequest.CreateOn <= _dteEnd
                         && subjectOpenRequest.Startus < 2
                         && subjectOpenRequest.Training_SubjectOpenRequestKeyParent == null
                         select new
                         {
                             subjectOpenRequest.Training_SubjectOpenRequestKey,
                             subjectOpenRequest.Training_WeekDayKey,
                             subjectOpenRequest.Training_SessionKey
                         };
            return iQuery;

        }

        public static IQueryable GetListByDateTimeAndSubject(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest, 
            DateTime _dteFrom, 
            DateTime _dteEnd, 
            int _iTraining_SubjectKey)
        {
            var iQuery = from subjectOpenRequest in nTraining_SubjectOpenRequest
                         where subjectOpenRequest.Startus < 2
                         where subjectOpenRequest.Training_SubjectKey == _iTraining_SubjectKey
                         select new
                         {
                             subjectOpenRequest.Training_SubjectOpenRequestKey,
                             subjectOpenRequest.Training_WeekDayKey,
                             subjectOpenRequest.Training_SessionKey
                         };
            return iQuery;
        }

        public static IQueryable GetListChildrent(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest, 
            int _iTraining_SubjectOpenRequestKey,
            bool getbykey = false)
        {
            if (getbykey)
            {
                var iQuery = nTraining_SubjectOpenRequest.Where(
                    p => p.Training_SubjectOpenRequestKeyParent == _iTraining_SubjectOpenRequestKey
                    || p.Training_SubjectOpenRequestKey == _iTraining_SubjectOpenRequestKey);
                return iQuery;
            }
            else
            {
                var iQuery = nTraining_SubjectOpenRequest.Where(
                    p => p.Training_SubjectOpenRequestKeyParent == _iTraining_SubjectOpenRequestKey);
                return iQuery;
            }
        }

        public static void SplitRequestJoined(
            this Table<Training_SubjectOpenRequest> nTraining_SubjectOpenRequest, 
            int _iTraining_SubjectOpenRequestKey)
        {
            DBMain DBMain = new DBMain();

            var iQuery = DBMain.m_DCSave.Training_SubjectOpenRequests.Where(
                p => p.Training_SubjectOpenRequestKeyParent == _iTraining_SubjectOpenRequestKey);
            foreach (var request in iQuery)
            {
                request.Training_SubjectOpenRequestKeyParent = null;
            }
            DBMain.m_DCSave.SubmitChanges();
        }
    }
}
